IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GXBZ_EMR_CYJL]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[USP_GXBZ_EMR_CYJL]
GO

/**********
[版本号]1.0.0
[创建时间]2018-12-13
[作者]SJ
[版权]卫宁健康科技集团股份有限公司
[描述] 
[功能说明]重庆cdc数据上传--出院记录GXBZ_EMR_CYJL数据上传
[返回值]
[调用的USP]
	[调用实例] EXEC USP_GXBZ_EMR_CYJL
[修改历史]
**********/
CREATE PROC [dbo].[USP_GXBZ_EMR_CYJL]    
AS 
  SET XACT_ABORT ON 
DECLARE 
	@kssj datetime,					--上传开始时间
	@jssj datetime					--上传结束时间
BEGIN                                                                                                  

	
	
	IF NOT EXISTS(SELECT table_time FROM SYS_SJSC_TABLE 
	WHERE TABLE_NAME = 'GXBZ_EMR_CYJL')
	BEGIN 
		INSERT INTO SYS_SJSC_TABLE(TABLE_NAME,TABLE_TIME)
		VALUES('GXBZ_EMR_CYJL','2015-07-01')
	END 
	
	--获取已上传的时间
	SELECT @kssj =TABLE_TIME  FROM SYS_SJSC_TABLE(NOLOCK)
	WHERE TABLE_NAME = 'GXBZ_EMR_CYJL'
	
	--获取源表中最大上传时间
	SELECT @jssj = MAX(TBRQ) FROM ZYCYXJBG(NOLOCK)
	
	IF(@jssj>ISNULL(@kssj,'1900-01-01'))
	BEGIN
		BEGIN TRY
		BEGIN TRAN

	-------创建临时表  
	CREATE TABLE #TEMP_GXBZ_EMR_CYJL(  
		ZYLSH                varchar(64)          not null,
		XGBZ                 varchar(1)           not null,
		GRJBXXBSH            varchar(64)          not null,
		CXJMJKDABH           varchar(17)          not null,
		CISID                char(10)             null,
		KFSSGH               varchar(64)          null,
		KFYSXM               varchar(30)          null,
		WDGLJGDM             varchar(22)          null,
		WDGLJGMC             varchar(70)          null,
		ZRYSBM               varchar(64)          null,
		ZRYSXM               varchar(30)          null,
		ZRYSQMSJ             datetime             null,
		ZZYSBM               varchar(64)          null,
		ZZYSXM               varchar(30)          null,
		ZYYSBM               varchar(64)          null,
		ZYYSXM               varchar(30)          null,
		BCH                  varchar(20)          null,
		BCHMC                varchar(100)         null,
		BFH                  varchar(20)          null,
		BFHMC                varchar(100)         null,
		KSBM                 varchar(64)          null,
		KSMC                 varchar(32)          null,
		CYBQBM               varchar(20)          null,
		CYBQMC               varchar(100)         null,
		YLJGDM               varchar(22)          null,
		YLJGMC               varchar(70)          null,
		ZYJKQK               varchar(1024)        null,
		RYZDBM               varchar(10)          null,
		RYRQSJ               datetime             null,
		YXFZJCJG             varchar(1024)        null,
		ZYSZGCJG             varchar(1024)        null,
		ZZZF                 varchar(100)         null,
		ZLGCMS               varchar(1024)        null,
		ZYJZFF               varchar(100)         null,
		ZYYYFF               varchar(100)         null,
		CYQK                 varchar(1024)        null,
		CYRQSJ               datetime             null,
		CXYZDDM              varchar(10)          null,
		CXYZDMC              varchar(100)         null,
		CZYBMMC              varchar(100)         null,
		CZYBMDM              varchar(10)          null,
		CZYZHMC              varchar(100)         null,
		CZYZHDM              varchar(10)          null,
		CYZZTZ               varchar(1024)        null,
		CYYZ                 varchar(1024)        null,
		MJ                   varchar(16)          not null,
		TBRQ                 datetime             not null,
		SCZT                 varchar(1)           not null,
		CS_JYBZ              int                  not null,
		SCSJ                 datetime             not null,
	) ON [PRIMARY]

	--插业务数据到临时表
	INSERT INTO #TEMP_GXBZ_EMR_CYJL (
			ZYLSH,
			XGBZ,
			GRJBXXBSH,
			CXJMJKDABH,
			CISID,
			KFSSGH,
			KFYSXM,
			WDGLJGDM,
			WDGLJGMC,
			ZRYSBM,
			ZRYSXM,
			ZRYSQMSJ,
			ZZYSBM,
			ZZYSXM,
			ZYYSBM,
			ZYYSXM,
			BCH,
			BCHMC,
			BFH,
			BFHMC,
			KSBM,
			KSMC,
			CYBQBM,
			CYBQMC,
			YLJGDM,
			YLJGMC,
			ZYJKQK,
			RYZDBM,
			RYRQSJ,
			YXFZJCJG,
			ZYSZGCJG,
			ZZZF,
			ZLGCMS,
			ZYJZFF,
			ZYYYFF,
			CYQK,
			CYRQSJ,
			CXYZDDM,
			CXYZDMC,
			CZYBMMC,
			CZYBMDM,
			CZYZHMC,
			CZYZHDM,
			CYZZTZ,
			CYYZ,
			MJ,
			TBRQ,
			SCZT,
			CS_JYBZ,
			SCSJ
					) 
	SELECT
		A.JZLSH AS ZYLSH,		--住院流水号
		A.XGBZ AS XGBZ,		--修改标志
		(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) AS GRJBXXBSH,		--个人基本信息标识号
		(select top 1 JBXX.CXJMJKDABH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) AS CXJMJKDABH,		--城乡居民健康档案编号
		A.JZLSH AS CISID,		--住院号
		A.CZYGH AS KFSSGH,		--文档作者工号
		A.CZYXM AS KFYSXM,		--文档作者姓名
		'500103000000001' AS WDGLJGDM,		--文档管理机构代码 
		'渝中区卫计委' AS WDGLJGMC,		--文档管理机构名称
		C.ZRYSBM AS ZRYSBM,		--主任医师工号
		C.ZRYSXM AS ZRYSXM,		--主任医师姓名
		A.QMRQSJ AS ZRYSQMSJ,		--主任医生签名时间
		C.ZZYSBM AS ZZYSBM,		--主治医师工号
		C.ZZYSXM AS ZZYSXM,		--主治医师姓名
		C.ZYYSBM AS ZYYSBM,		--住院医生工号
		C.ZYYSXM AS ZYYSXM,		--住院医生姓名
		A.CH AS BCH,		--病床号
		'' AS BCHMC,		--病床号名称
		'' AS BFH,		--病房号
		'' AS BFHMC,		--病房号名称
		A.KSDM AS KSBM,		--科室编码
		A.KSMC AS KSMC,		--科室名称
		'' AS CYBQBM,		--出院病区编码
		'' AS CYBQMC,		--出院病区名称
		ISNULL((SELECT SPTDM FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGDM,		--医疗机构代码
		ISNULL((SELECT JGMC FROM SYS_ZCJG_DZ where A.YLJGDM=QPTDM),'500103000000001') AS YLJGMC,		--医疗机构名称
		A.RYZZTZ AS ZYJKQK,		--主要健康问题描述
		'' AS RYZDBM,		--入院诊断编码
		A.RYSJ AS RYRQSJ,		--入院诊断日期
		D.FZJCJL AS YXFZJCJG,		--阳性辅助检查结果
		D.ZYSZMS AS ZYSZGCJG,		--中医"四诊"观察结果
		'' AS ZZZF,		--治则治法
		A.ZLGC AS ZLGCMS,		--诊疗过程描述
		'' AS ZYJZFF,		--中药煎煮方法
		'' AS ZYYYFF,		--中药用药方法
		A.CYQK AS CYQK,		--出院情况
		A.CYSJ AS CYRQSJ,		--出院日期时间
		A.CXYZDDM AS CXYZDDM,		--出院诊断-西医诊断编码
		A.CXYZDMC AS CXYZDMC,		--出院诊断-西医诊断名称
		A.CZYBMMC AS CZYBMMC,		--出院诊断-中医病名名称
		A.CZYBMDM AS CZYBMDM,		--出院诊断-中医病名代码
		A.CZYZHMC AS CZYZHMC,		--出院诊断-中医症候名称
		A.CZYZHDM AS CZYZHDM,		--出院诊断-中医症候代码
		A.CYQK  AS CYZZTZ,		--出院时症状与体征
		A.CYYZ AS CYYZ,		--出院医嘱
		A.MJ AS MJ,		--（系统）密级
		A.TBRQ AS TBRQ,		--（系统）填报日期
		'0' AS SCZT,		--（系统）上传状态
		'0' AS CS_JYBZ,		--（系统）校验标志
		GETDATE() ASSCSJ		--（系统）上传时间 
	FROM ZYCYXJBG A(NOLOCK)
	LEFT JOIN ZY_BASY_PGBG C(NOLOCK) ON A.YLJGDM=C.YLJGDM AND A.JZLSH=C.JZLSH  
	LEFT JOIN ZYJZJLB D(NOLOCK) ON A.YLJGDM = D.YLJGDM AND A.JZLSH = D.JZLSH
	WHERE a.TBRQ>ISNULL(@kssj,'1900-01-01')
	and 
	(select top 1 JBXX.GRJBXXBSH from GXBZ_DA_GR_JBXX JBXX where JBXX.zjhm = C.ZJHM) is not NULL
	
	
	--删除中间库中重复数据
	DELETE A FROM GXBZ_EMR_CYJL A,#TEMP_GXBZ_EMR_CYJL B
	WHERE A.GRJBXXBSH=B.GRJBXXBSH AND A.YLJGDM=B.YLJGDM AND A.ZYLSH=B.ZYLSH

	--插入数据至中间库
	INSERT INTO GXBZ_EMR_CYJL (ZYLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CISID,KFSSGH,KFYSXM,WDGLJGDM,WDGLJGMC,ZRYSBM,ZRYSXM,ZRYSQMSJ,ZZYSBM,
		ZZYSXM,ZYYSBM,ZYYSXM,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,CYBQBM,CYBQMC,YLJGDM,YLJGMC,ZYJKQK,RYZDBM,RYRQSJ,YXFZJCJG,ZYSZGCJG,ZZZF,
		ZLGCMS,ZYJZFF,ZYYYFF,CYQK,CYRQSJ,CXYZDDM,CXYZDMC,CZYBMMC,CZYBMDM,CZYZHMC,CZYZHDM,CYZZTZ,CYYZ,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ)    
	SELECT ZYLSH,XGBZ,GRJBXXBSH,CXJMJKDABH,CISID,KFSSGH,KFYSXM,WDGLJGDM,WDGLJGMC,ZRYSBM,ZRYSXM,ZRYSQMSJ,ZZYSBM,
		ZZYSXM,ZYYSBM,ZYYSXM,BCH,BCHMC,BFH,BFHMC,KSBM,KSMC,CYBQBM,CYBQMC,YLJGDM,YLJGMC,ZYJKQK,RYZDBM,RYRQSJ,YXFZJCJG,ZYSZGCJG,ZZZF,
		ZLGCMS,ZYJZFF,ZYYYFF,CYQK,CYRQSJ,CXYZDDM,CXYZDMC,CZYBMMC,CZYBMDM,CZYZHMC,CZYZHDM,CYZZTZ,CYYZ,MJ,TBRQ,SCZT,CS_JYBZ,SCSJ
	FROM #TEMP_GXBZ_EMR_CYJL

	COMMIT TRAN

	   SELECT 'T' AS SRESULT, '表：GXBZ_EMR_CYJL数据抽取成功！' AS SHINT
   
	   INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME) 
	   VALUES('表：GXBZ_EMR_CYJL数据抽取成功','GXBZ_EMR_CYJL',GETDATE(),'T','USP_WD_GXBZ_EMR_CYJL')  
   
	 -- INSERT INTO SYS_SJSC_TABLENAME(TABLE_NAME, SCKSSJ, SCJSSJ, BZ)
	   --VALUES('GXBZ_EMR_CYJL',@sKssj,@sJssj,'')
		--更新上传时间
		UPDATE SYS_SJSC_TABLE SET TABLE_TIME = @jssj WHERE TABLE_NAME = 'GXBZ_EMR_CYJL'
		
		

	END TRY BEGIN CATCH
		SELECT 'F' AS SRESULT, '表：GXBZ_EMR_CYJL数据抽取失败！' AS SHINT, ERROR_MESSAGE() AS MSG
		ROLLBACK TRAN

		/*记录每步执行的记录日志*/
		INSERT INTO SYS_SCSJ_LOG(CONTENT,TABLENAME,RECORDTIME,RST,PROCNAME,MSG,ERRORCODE) 
		VALUES('表：GXBZ_EMR_CYJL数据抽取失败','GXBZ_EMR_CYJL',GETDATE(),'F','USP_GXBZ_EMR_CYJL',ERROR_MESSAGE(),ERROR_NUMBER())
	END CATCH
	END
END

